'''
@Project ：python-study
@File    ：8.10练习2：电影语言频数统计.py
@IDE     ：PyCharm
@Author  ：SUNLIN
@Date    ：2025/3/12 9:22:08
'''

import pandas as pd
import numpy as np

pd.set_option('display.expand_frame_repr', False)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.width', 100)

data = pd.read_excel('../doc/datasource/C8-8.5-数据采集-clean.xlsx',
                     usecols=['movie_name', 'country', 'language', 'release_date', 'average_score', ])
# 按照所定义顺序排序
data = data[['movie_name', 'country', 'language', 'release_date', 'average_score']]

# 防止有空值
data['language'] = data['language'].fillna(value='')
data['language'] = data['language'].str.strip(' ')

# 获取去重之后的国家列表
language_list = []
for c in data['language']:
    c_list = c.split(' / ')
    for l in c_list:
        language_list.append(l)
language_list = list(set(language_list))

if '' in language_list:
    language_list.remove('')
if '汉语普通话' in language_list:
    language_list.remove('汉语普通话')

data_lang_tj = pd.DataFrame(np.zeros([len(language_list), 1]), index=language_list, columns=['tj'])

# 遍历方法1
for c in language_list:
    for c1 in data['language']:
        if str(c1).__contains__(c):
            data_lang_tj.loc[c, 'tj'] += 1
# 遍历方法2
# for c in data['language']:
#     for c1 in language_list:
#         if str(c).__contains__(c1):
#             data_lang_tj.loc[c1, 'tj'] += 1


# 将小类汇总成大类，并添加至总得数据
hnh_count = 0
if '湖南话' in data_lang_tj:
    hnh_count = data_lang_tj['湖南话', 'tj']
bjh_count = 0
if '北京话' in data_lang_tj:
    bjh_count = data_lang_tj['北京话', 'tj']

chinese_fy = hnh_count + bjh_count
data_lang_tj.loc['中国方言', 'tj'] = chinese_fy
print(data_lang_tj)
